*WHAT'S ACROSS THE BORDER?
*JHA, NEUMARK, AND RODRIGUEZ-LOPEZ

include directories.do


putexcel set "`outdir'`s'TablesJNR.xlsx", sheet(Table8) modify

foreach geo in "co" "cz" {
* Load data at the czone-state-industry level
use "`dlrdir'`s'QCEWindustry_minwage_contig_`geo'.dta", clear
egen id_`geo'=group(pair_id countyreal)
tsset  id_`geo' period
 
 forval j = 4(4)16 {
 gen empshock`j'=4*(100/`j')*(F`j'.lnemp_TOT-lnemp_TOT)
 gen popshock`j'=4*(100/`j')*(F`j'.lnpop-lnpop)
}

collapse (median) empshock* popshock*, by(countyreal period)

tempfile shocks
save `shocks'


*Pairs
 
 use "`dlrdir'`s'DLR_complete_`geo'_pairs_period.dta", clear

foreach v in "0" "1" {
ren countyreal_`v' countyreal
merge m:1 period countyreal using `shocks'
keep if _merge==3
drop _merge
ren countyreal countyreal_`v'
forval j = 4(4)16 {
ren empshock`j' empshock`j'_`v'
ren popshock`j' popshock`j'_`v'
}
}

local window4  period==26 | period==30 | period==34 | period==38 | period==42 | period==46 | period==50 | period==54 | period==58 | period==62 | period==66 | period==70 | period==74 | period==78 | period==82 | period==86 | period==90
local window8  period==26 | period==34 | period==42 | period==50 | period==58 | period==66 | period==74 | period==82 | period==90
local window12  period==26 | period==38 | period==50 | period==62 | period==74 | period==86
local window16  period==26 | period==42 | period==58 | period==74 | period==90

* Correlations

mat A=[0]
mat B=[0]

forval j = 4(4)16 {
quietly corr empshock`j'_0 empshock`j'_1 if (`window`j'')
mat A`j'=r(C)
mat A=[A, A`j'[2,1]]
}
forval j = 4(4)16 {
quietly corr popshock`j'_0 popshock`j'_1 if (`window`j'')
mat B`j'=r(C)
mat B=[B, B`j'[2,1]]
}


matrix dlr_`geo' = [A[1,2..5] \ B[1,2..5]] 

matrix list dlr_`geo', format(%4.2f)


}

******  CBP YEARLY DATA ******************


**** CZONE 

* Load data at the czone-state-industry level
use "`cbpdir'`s'cbp_czone_state_wages_20ind_1990_2016", clear

drop if year==.


 drop if state==11
 bys year czone state: egen totempgeo=total(emp)
 gen totempgeom=totempgeo-emp
 keep if sic87xx==5800
 egen id=group(czone state)
 tsset id year
 gen logtotempgeom=log(totempgeom)
 gen logworkagepop=log(workagepop)
 
 forval j = 1(1)4 {
 gen empshock`j'=(100/`j')*(F`j'.logtotempgeom-logtotempgeom)
 gen popshock`j'=(100/`j')*(F`j'.logworkagepop-logworkagepop)
}

tempfile shocks
save `shocks'


*Pairs
 
 use "`czonedir'`s'cbp_czone_pairs", clear

 foreach v in "0" "1" {
ren state_`v' state 

merge m:1 year czone state using `shocks'
keep if _merge==3
drop _merge
ren state state_`v'
forval j = 1(1)4 {
ren empshock`j' empshock`j'_`v'
ren popshock`j' popshock`j'_`v'
}
ren totempgeom totempgeom_`v'
ren workagepop workagepop_`v'
}


*Windows
local window1 year==1990 | year==1991 | year==1992 | year==1993 | year==1994 | year==1995 | year==1996 | year==1997 | year==1998  | year==1999 | year==2000 | year==2001 | year==2002 | year==2003 | year==2004 | year==2005 | year==2006 | year==2007 | year==2008 | year==2009 | year==2010 | year==2011 | year==2012 | year==2013 | year==2014 | year==2015 | year==2016
local window2 year==1990 | year==1992 | year==1994 | year==1996 | year==1998 | year==2000 | year==2002 | year==2004 | year==2006 | year==2008 | year==2010 | year==2012 | year ==2014
local window3 year==1990 | year==1993 | year==1996 | year==1999 | year==2002 | year==2005 | year==2008 | year==2011
local window4 year==1990 | year==1994 | year==1998 | year==2002 | year==2006 | year==2010 | year==2014 


egen id2=group(pair_id)
replace id=id2
tsset id year


* Correlations

mat A=[0]
mat B=[0]
mat D=[0]
mat E=[0]
forval j = 1(1)4 {
quietly corr empshock`j'_0 empshock`j'_1 if (`window`j'')
mat A`j'=r(C)
mat A=[A, A`j'[2,1]]
}
forval j = 1(1)4 {
quietly corr popshock`j'_0 popshock`j'_1 if (`window`j'')
mat B`j'=r(C)
mat B=[B, B`j'[2,1]]
}

*DLR pairs
forval j = 1(1)4 {
quietly corr empshock`j'_0 empshock`j'_1 if pairtimes==2 & (`window`j'')
mat D`j'=r(C)
mat D=[D, D`j'[2,1]]
}

forval j = 1(1)4 {
quietly corr popshock`j'_0 popshock`j'_1 if pairtimes==2 & (`window`j'')
mat E`j'=r(C)
mat E=[E, E`j'[2,1]]
}


matrix cbp_cz = [A[1,2..5] \ B[1,2..5]] 
matrix cbp_cz_dlr = [D[1,2..5]\ E[1,2..5]] 

matrix list cbp_cz, format(%4.2f)
matrix list cbp_cz_dlr, format(%4.2f)


** COUNTY

use "`cbpdir'`s'cbp_county_state_wages_20ind_1990_2016", clear


drop if year==.
*gen state=int(cty_fips/1000)

drop if state==2
drop if state==15
drop if state==11
 
bys year cty_fips: egen totempgeo=total(emp)
 gen totempgeom=totempgeo-emp
 keep if sic87xx==5800
 tsset cty_fips year
 gen logtotempgeom=log(totempgeom)
 gen logworkagepop=log(workagepop)
 
 forval j = 1(1)4 {

 gen empshock`j'=(100/`j')*(F`j'.logtotempgeom-logtotempgeom)
 gen popshock`j'=(100/`j')*(F`j'.logworkagepop-logworkagepop)
}

tempfile shocks
save `shocks'

*Pairs
 
 use "`czonedir'`s'cbp_county_pairs", clear

foreach v in "0" "1" {
ren cty_fips_`v' cty_fips
merge m:1 year cty_fips using `shocks'
keep if _merge==3
drop _merge
ren cty_fips cty_fips_`v'
forval j = 1(1)4 {
ren empshock`j' empshock`j'_`v'
ren popshock`j' popshock`j'_`v'
}
ren totempgeom totempgeom_`v'
ren workagepop workagepop_`v'
}


* Correlations


mat A=[0]
mat B=[0]
mat D=[0]
mat E=[0]
forval j = 1(1)4 {
quietly corr empshock`j'_0 empshock`j'_1 if (`window`j'')
mat A`j'=r(C)
mat A=[A, A`j'[2,1]]
}
forval j = 1(1)4 {
quietly corr popshock`j'_0 popshock`j'_1 if (`window`j'')
mat B`j'=r(C)
mat B=[B, B`j'[2,1]]
}

*DLR pairs
forval j = 1(1)4 {
quietly corr empshock`j'_0 empshock`j'_1 if pairtimes==2 & (`window`j'')
mat D`j'=r(C)
mat D=[D, D`j'[2,1]]
}

forval j = 1(1)4 {
quietly  corr popshock`j'_0 popshock`j'_1 if pairtimes==2 & (`window`j'')
mat E`j'=r(C)
mat E=[E, E`j'[2,1]]
}


matrix cbp_co = [A[1,2..5] \ B[1,2..5]] 
matrix cbp_co_dlr = [D[1,2..5]\ E[1,2..5]] 

matrix list cbp_co, format(%4.2f)
matrix list cbp_co_dlr, format(%4.2f)


********* BARTIK ****************

*CZONE
 
 use "`czonedir'`s'cbp_czone_pairs", clear

 
preserve

use "`cbpdir'`s'cbp_czone_state_1990_2016", clear


drop if year==.


 drop if state==11
 bys year sic87dd: egen totempind=total(emp)
 gen totempindm=totempind-emp
 bys year czone state: egen totempgeo=total(emp)
 egen id=group(sic87dd czone state)
 tsset id year
 gen logtotempindm=log(totempindm)
 

  forval j = 1(1)4 {
 gen bartik`j'=(emp/totempgeo)*(100/`j')*(F`j'.logtotempindm-logtotempindm)
}
 collapse (sum) bartik*, by(czone state year)

  forval j = 1(1)4 {
replace bartik`j'=. if bartik`j'==0
}
tempfile tempbartik1
save `tempbartik1', replace

restore


ren state_0 state 

merge m:1 year czone state using "`tempbartik1'"
keep if _merge==3
drop _merge
ren state state_0
 forval j = 1(1)4 {
ren bartik`j' bartik`j'_0
}

ren state_1 state 

merge m:1 year czone state using "`tempbartik1'"
keep if _merge==3
drop _merge
ren state state_1
 forval j = 1(1)4 {
ren bartik`j' bartik`j'_1
}

*Windows
local window1 year==1990 | year==1991 | year==1992 | year==1993 | year==1994 | year==1995 | year==1996 | year==1997 | year==1998  | year==1999 | year==2000 | year==2001 | year==2002 | year==2003 | year==2004 | year==2005 | year==2006 | year==2007 | year==2008 | year==2009 | year==2010 | year==2011 | year==2012 | year==2013 | year==2014 | year==2015 | year==2016
local window2 year==1990 | year==1992 | year==1994 | year==1996 | year==1998 | year==2000 | year==2002 | year==2004 | year==2006 | year==2008 | year==2010 | year==2012 | year ==2014
local window3 year==1990 | year==1993 | year==1996 | year==1999 | year==2002 | year==2005 | year==2008 | year==2011
local window4 year==1990 | year==1994 | year==1998 | year==2002 | year==2006 | year==2010 | year==2014 


* Correlations


mat A=[0]
mat B=[0]
forval j = 1(1)4 {
quietly corr bartik`j'_0 bartik`j'_1 if (`window`j'')
mat A`j'=r(C)
mat A=[A, A`j'[2,1]]
}
forval j = 1(1)4 {
quietly corr bartik`j'_0 bartik`j'_1 if pairtimes==2 & (`window`j'')
mat B`j'=r(C)
mat B=[B, B`j'[2,1]]
}



matrix bartik_cz = [A[1,2..5]]
matrix bartik_cz_dlr = [ B[1,2..5]]  

matrix list bartik_cz, format(%4.2f)
matrix list bartik_cz_dlr, format(%4.2f)


** COUNTY


*Pairs
 
use "`czonedir'`s'cbp_county_pairs", clear

preserve


use "`cbpdir'`s'cbp_county_1990_2016", clear


drop if year==.
gen state=int(cty_fips/1000)

drop if state==2
drop if state==15
drop if state==11
 
bys year sic87dd: egen totempind=total(emp)
 gen totempindm=totempind-emp
 bys year cty_fips: egen totempgeo=total(emp)
 egen id=group(sic87dd cty_fips)
 tsset id year
 gen logtotempindm=log(totempindm)

 forval j = 1(1)4 {
 gen bartik`j'=(emp/totempgeo)*(100/`j')*(F`j'.logtotempindm-logtotempindm)
} 

 collapse (sum) bartik*, by(cty_fips year)

  forval j = 1(1)4 {
replace bartik`j'=. if bartik`j'==0
}
 
tempfile tempbartik2
save `tempbartik2', replace

restore

ren cty_fips_0 cty_fips

merge m:1 year cty_fips using "`tempbartik2'"
keep if _merge==3
drop _merge
ren cty_fips cty_fips_0
 forval j = 1(1)4 {
ren bartik`j' bartik`j'_0
}


ren cty_fips_1 cty_fips


merge m:1 year cty_fips using "`tempbartik2'"
keep if _merge==3
drop _merge
ren cty_fips cty_fips_1
 forval j = 1(1)4 {
ren bartik`j' bartik`j'_1
}

								
* Correlations

mat A=[0]
mat B=[0]
forval j = 1(1)4 {
quietly corr bartik`j'_0 bartik`j'_1 if (`window`j'')
mat A`j'=r(C)
mat A=[A, A`j'[2,1]]
}
forval j = 1(1)4 {
quietly corr bartik`j'_0 bartik`j'_1 if pairtimes==2 & (`window`j'')
mat B`j'=r(C)
mat B=[B, B`j'[2,1]]
}



matrix bartik_co = [A[1,2..5]]
matrix bartik_co_dlr = [ B[1,2..5]]  

matrix list bartik_co, format(%4.2f)
matrix list bartik_co_dlr, format(%4.2f)


putexcel A1= "DLR's Quarterly Data"
putexcel A2 = matrix(dlr_co), nformat(number_d2)
putexcel E2 = matrix(dlr_cz), nformat(number_d2)

putexcel A4= "CBP Yearly Data -- All Complete Pairs"
putexcel A5 = matrix(cbp_co), nformat(number_d2)
putexcel E5 = matrix(cbp_cz), nformat(number_d2)
putexcel A7 = matrix(bartik_co), nformat(number_d2)
putexcel E7 = matrix(bartik_cz), nformat(number_d2)

putexcel A8= "CBP Yearly Data -- DLR's Complete Pairs"
putexcel A9 = matrix(cbp_co_dlr), nformat(number_d2)
putexcel E9 = matrix(cbp_cz_dlr), nformat(number_d2)
putexcel A11 = matrix(bartik_co_dlr), nformat(number_d2)
putexcel E11 = matrix(bartik_cz_dlr), nformat(number_d2)

clear
